﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Schema;

namespace DbProfiler.DatabaseAccess.SchemaExtraction
{
    public class OleDbSchemaExtractor : SchemaExtractorBase, ISchemaExtractor
    {
        public Database Database { get; set; }

        public XmlDocument ReadSchema()
        {          
            // Create the XML document and add the schema to it.
            XmlDocument xdoc = new XmlDocument();
            string schemaPath = @"D:\Thomas\Development\Projects\Database Profiler\Implementation\Database Access Library\SchemaExtraction\DatabaseSchema.xsd";
            XmlSchema xmlSchema = XmlSchema.Read(new System.IO.StreamReader(schemaPath), null);
            xdoc.Schemas.Add(xmlSchema);

            // Create the connection. Because we wan't to be independent from the database type stored in the database property, we
            // don't use the connectionFactory.
            OleDbConnection con = new OleDbConnection(Database.ConnectionString);
            con.Open();

            // Create the root element <DatabaseSchema>   
            XmlNode xndDatabaseSchema = xdoc.CreateElement("DatabaseSchema");
            xdoc.AppendChild(xndDatabaseSchema);

            // Get the description of the database.
            string dbDescription = getDatabaseDescription(con);

            // Add attributes of the database to the root node.
            xndDatabaseSchema.Attributes.Append(xdoc.CreateAttribute("Name")).Value = Database.Name;
            if (dbDescription.Trim() != string.Empty)
            {
                xndDatabaseSchema.Attributes.Append(xdoc.CreateAttribute("Description")).Value = dbDescription;
            }           




            // Create the tables-root node <Tables>
            XmlNode xndTables = xdoc.CreateElement("Tables");
            xndDatabaseSchema.AppendChild(xndTables);

            // Get information about all user-tables.
            DataTable dtTables = con.GetOleDbSchemaTable(
                OleDbSchemaGuid.Tables_Info,
                new object[] { Database.Name, null, null, "TABLE" });

            // Loop over each user-table and create the table xml node <Table ....>
            foreach (DataRow rwTableInfo in dtTables.Rows)
            {
                // Create the table xml node.
                XmlNode xndTable = xdoc.CreateElement("Table");
                xndTables.AppendChild(xndTable);                

                // Get Information about the table
                string schemaName = rwTableInfo["TABLE_SCHEMA"].ToString();
                string tableName = rwTableInfo["TABLE_NAME"].ToString();                
                string tblDescription = getTableDescription(con, schemaName, tableName);

                // Add the attributes of the table to the xml-node.
                xndTable.Attributes.Append(xdoc.CreateAttribute("Schema")).Value = schemaName;
                xndTable.Attributes.Append(xdoc.CreateAttribute("Name")).Value = tableName;
                if (tblDescription.Trim() != string.Empty)
                {
                    xndTable.Attributes.Append(xdoc.CreateAttribute("Description")).Value = tblDescription;
                }



                // Get information about all columns of the table
                DataTable dtColumns = con.GetOleDbSchemaTable(
                    OleDbSchemaGuid.Columns,
                    new object[] { Database.Name, schemaName, tableName });

                // Loop over each table column and create the column xml node <TableColumn ...>
                foreach (DataRow rwColumn in dtColumns.Rows)
                {
                    // Create the column node.
                    XmlNode xndColumn = xdoc.CreateElement("TableColumn");
                    xndTable.AppendChild(xndColumn);

                    // Get Information about the column
                    string columnName = rwColumn["COLUMN_NAME"].ToString();
                    int columnTypeNumber = Convert.ToInt32(rwColumn["DATA_TYPE"]);
                    string columnDescription = getTableColumnDescription(con, schemaName, tableName, columnName, Convert.ToInt32(rwColumn["ORDINAL_POSITION"]));

                    // Add the attributes of the column to the xml-node.
                    xndColumn.Attributes.Append(xdoc.CreateAttribute("Name")).Value = columnName;
                    xndColumn.Attributes.Append(xdoc.CreateAttribute("Type")).Value = getTableColumnType(columnTypeNumber);
                    if (columnDescription.Trim() != string.Empty)
                    {
                        xndColumn.Attributes.Append(xdoc.CreateAttribute("Description")).Value = columnDescription;
                    }                    
                }
            }


            // Create relations root-node.
            XmlNode xndRelations = xdoc.CreateElement("Relations");
            xndDatabaseSchema.AppendChild(xndRelations);

            // Get every relation of the database
            DataTable dtRelations = con.GetOleDbSchemaTable(
                OleDbSchemaGuid.Foreign_Keys,
                new object[] { });
            foreach (DataRow rwRelation in dtRelations.Rows)
            {
                string pk_schemaName = rwRelation["PK_TABLE_SCHEMA"].ToString();
                string pk_tableName = rwRelation["PK_TABLE_NAME"].ToString();
                string pk_columnName = rwRelation["PK_COLUMN_NAME"].ToString();

                string fk_schemaName = rwRelation["FK_TABLE_SCHEMA"].ToString();
                string fk_tableName = rwRelation["FK_TABLE_NAME"].ToString();
                string fk_columnName = rwRelation["FK_COLUMN_NAME"].ToString();

                XmlNode xndRelation = xdoc.CreateElement("Relation");
                xndRelations.AppendChild(xndRelation);

                XmlNode xndRelationPK = xdoc.CreateElement("PrimaryKey");
                xndRelation.AppendChild(xndRelationPK);
                xndRelationPK.Attributes.Append(xdoc.CreateAttribute("Schema")).Value = pk_schemaName;
                xndRelationPK.Attributes.Append(xdoc.CreateAttribute("Table")).Value = pk_tableName;
                xndRelationPK.Attributes.Append(xdoc.CreateAttribute("Column")).Value = pk_columnName;

                XmlNode xndRelationFK = xdoc.CreateElement("ForeignKey");
                xndRelation.AppendChild(xndRelationFK);
                xndRelationFK.Attributes.Append(xdoc.CreateAttribute("Schema")).Value = fk_schemaName;
                xndRelationFK.Attributes.Append(xdoc.CreateAttribute("Table")).Value = fk_tableName;
                xndRelationFK.Attributes.Append(xdoc.CreateAttribute("Column")).Value = fk_columnName;
            }

            return xdoc;
        }

        private string getDatabaseDescription(OleDbConnection con)
        {
            DataTable dtDB = con.GetOleDbSchemaTable(
                OleDbSchemaGuid.Catalogs,
                new object[] { Database.Name });
            if (dtDB.Rows.Count == 1)
            {
                return dtDB.Rows[0]["DESCRIPTION"].ToString();
            }
            else
            {
                return string.Empty;
            }
        }

        private string getTableDescription(OleDbConnection con, string schemaName, string tableName)
        {
//            string sqlTblDescription = @"SELECT  
//			                                value as [TABLE_DESCRIPTION]
//		                                FROM 
//			                                sys.extended_properties 
//		                                WHERE 
//			                                (class = 1) 
//			                                AND (minor_id = 0) 
//			                                AND (major_id = OBJECT_ID(N'[" + schema + "].[" + tableName + "]'))";
//            OleDbCommand cmdTblDescription = new OleDbCommand(sqlTblDescription, con);
//            IDbDataAdapter daTblDescription = new OleDbDataAdapter(cmdTblDescription);
//            DataSet ds = new DataSet();
//            daTblDescription.Fill(ds);

            return string.Empty;
        }

        private string getTableColumnDescription(OleDbConnection con, string schemaName, string tableName, string columnName, int columnId)
        {
            string sql = @"SELECT  
	                            value
                            FROM 
                                sys.extended_properties 
                            WHERE 
                                (class = 1) 
                                AND (minor_id = " + columnId.ToString() + @") 
                                AND (major_id = OBJECT_ID(N'[" + schemaName + "].[" + tableName + "]'))";
            OleDbCommand cmd = new OleDbCommand(sql, con);
            object o = cmd.ExecuteScalar();

            if (o != null)
            {
                return o.ToString();
            }
            else
            {
                return string.Empty;
            }
        }

        private string getTableColumnType(int columnTypeNumber)
        {
            try
            {
                Type type = MsftSqlTypeMappingFactory.MapType(columnTypeNumber);
                return type.ToString();
            }
            catch (NotImplementedException)
            {
                return "Unknown";
            }
        }
    }
}
